****************************
*Table 1: Sample composition
****************************
local mresult2 "Results/Table1.xls"
tempfile final_groups final_total_firms final_UP_firms final_Delo_firms
tempfile econ_groups econ_total_firms econ_UP_firms econ_Delo_firms
tempfile jscreg_sreg_groups jscreg_sreg_total_firms jscreg_sreg_UP_firms jscreg_sreg_Delo_firms
tempfile original_groups original_total_firms original_UP_firms original_Delo_firms

set more off
*use hiding variables from network analysis
use "Network Data/DUP_hiding_04012004.dta",clear
gen year=2004
append using "Network Data/DUP_hiding_11102006.dta"
replace year=2006 if year==.

*merge with data on presence of each firm in Delo, UP or both
merge m:1 okpo using "Prepared Data/Delo_UP_presence.dta"
drop _merge
sort okpo year

* assign political affiliation dummy: 1-blue, 2-orange, 3-gray
* note that we start with 34 oligarch/(s) groups, but regression sample includes only 26 of them because of the economic data availability
gen color=1 if  group=="Derkach" | group=="Energo" | group=="Franchuk" | group=="Interpipe" | group=="Khmelnytskyi" | group=="Kirpa" | group=="Kliuev" | group=="Kyiv-Seven" | group=="Old Donetsk" | group=="Radon" | group=="SCM" | group=="Tabachnyk" | group=="TAS" | group=="Ukrinterproduct" //14
replace color=2 if group=="Aval" | group=="Brinkford" | group=="Finansy i Kredyt" | group=="Ignatenko" | group=="Ivchenko" | group=="Omelchenko" | group=="Orlan" | group=="Rymaruk" | group=="Tretiakov" | group=="UkrPromInvest" | group=="Yushchenko" | group=="Zagreva" | group=="Pryvat" //15
replace color=3 if group=="Basis" | group=="Feldman" | group=="Intercontact"  | group=="ISD" | group=="Kahanovskyi" | group=="UkrSotsBank" | group=="UkrSybBank" | group=="double SCM/ISD" | group=="double ISD/Pryvat" //9

gen blue=1 if color==1
replace blue=0 if blue==.
gen orange=1 if color==2
replace orange=0 if orange==.
gen gray=1 if color==3
replace gray=0 if gray==.

*Table 1, Row 1: Original list
encode group, gen(gr)
gen original=1

*Delo original
preserve
collapse (count) okpo, by(Delo year original)
keep if year==2004 & Delo==1
drop Delo
rename okpo Delo
save `original_Delo_firms'
restore

*UP original
preserve
collapse (count) okpo, by(UP year original)
keep if year==2004 & UP==1
drop UP
rename okpo UP
save `original_UP_firms'
restore

*Total original
preserve
collapse (count) okpo, by(year original)
keep if year==2004
rename okpo total
save `original_total_firms'
restore

*Number of groups originial
preserve
duplicates drop gr, force
*don't count double groups
drop if strpos(group, "double")
collapse (count) gr, by(year original)
keep if year==2004
save `original_groups'
restore

**Table 1, Row 2: Present in JSCReg or SReg
gen jscreg_sreg=1 if present_JSCReg_SReg==1
replace jscreg_sreg=0 if jscreg_sreg==.

*Delo: present in JSCReg or SReg
preserve
collapse (count) okpo, by(Delo year jscreg_sreg)
keep if year==2004 & Delo==1 & jscreg_sreg==1
drop Delo
rename okpo Delo
save `jscreg_sreg_Delo_firms'
restore

*UP: present in JSCReg or SReg
preserve
collapse (count) okpo, by(UP year jscreg_s)
keep if year==2004 & UP==1 & jscreg_sreg==1
drop UP
rename okpo UP
save `jscreg_sreg_UP_firms'
restore

*Total: present in JSCReg or SReg
preserve
collapse (count) okpo, by(year jscreg_s)
keep if year==2004 & jscreg_sreg==1
rename okpo total
save `jscreg_sreg_total_firms'
restore

*Number of groups: present in JSCReg or SReg
preserve
keep if year==2004
sort group jscreg_sreg
quietly by group jscreg_sreg:  gen dup = cond(_N==1,0,_n)
drop if dup>1
drop dup
*don't count double groups
drop if strpos(group, "double")
collapse (count) gr, by(year jscreg_sreg)
keep if year==2004 & jscreg_sreg==1
save `jscreg_sreg_groups'
restore

**Table 1, Row 3: + sector and controls
merge m:1 okpo using "Prepared Data/sector.dta"
drop _merge
merge m:1 okpo  using "Prepared Data/econ.dta"
drop _merge
*add privatized dummy
merge m:1 okpo using "Prepared Data/privatized.dta"
drop _merge
*add regions and IV data
merge m:1 okpo using "Prepared Data/IV.dta"
drop _merge

*restrict sample to firms with economic data
gen economic=1 if lemp!=. & tfp!=. & privatized!=. 
replace economic=0 if economic==.

*Delo: + sector and controls
preserve
collapse (count) okpo, by(Delo year economic jscreg_sreg)
keep if year==2004 & Delo==1 & economic==1 & jscreg_sreg==1
drop Delo
rename okpo Delo
save `econ_Delo_firms'
restore

*UP: + sector and controls
preserve
collapse (count) okpo, by(UP year economic jscreg_sreg)
keep if year==2004 & UP==1 & economic==1 & jscreg_sreg==1
drop UP
rename okpo UP
save `econ_UP_firms'
restore

*Total: + sector and controls
preserve
collapse (count) okpo, by(year economic jscreg_sreg)
keep if year==2004 & economic==1 & jscreg_sreg==1
rename okpo total
save `econ_total_firms'
restore

*Number of groups: + sector and controls
preserve
keep if year==2004
sort group jscreg_sreg economic
quietly by group jscreg_sreg economic:  gen dup = cond(_N==1,0,_n)
drop if dup>1
drop dup
*don't count double groups
drop if strpos(group, "double")
keep if jscreg_sreg==1
collapse (count) gr, by(year economic jscreg_sreg)
keep if year==2004 & economic==1 & jscreg_sreg==1
save `econ_groups'
restore


**Table 1, Row 4: +at least 1 identified owner
gen nown2004=1 if has_owners==0 & year==2004
bysort okpo: egen nown=max(nown2004)
gen owner=0 if nown==1
replace owner=1 if owner==.

preserve
*Delo: +at least 1 identified owner
collapse (count) okpo, by(Delo year economic jscreg_sreg owner)
keep if year==2004 & Delo==1 & economic==1 & jscreg_sreg==1 & owner==1
drop Delo
rename okpo Delo
save `final_Delo_firms'
restore

*UP: +at least 1 identified owner
preserve
collapse (count) okpo, by(UP year economic jscreg_sreg owner)
keep if year==2004 & UP==1 & economic==1 & jscreg_sreg==1 & owner==1
drop UP
rename okpo UP
save `final_UP_firms'
restore

*Total: +at least 1 identified owner
preserve
collapse (count) okpo, by(year economic jscreg_sreg owner)
keep if year==2004 & economic==1 & jscreg_sreg==1 & owner==1
rename okpo total
save `final_total_firms'
restore

*Number of groups: +at least 1 identified owner
preserve
keep if year==2004
sort group jscreg_sreg economic owner
quietly by group jscreg_sreg economic owner:  gen dup = cond(_N==1,0,_n)
drop if dup>1
drop dup
*don't count double groups
drop if strpos(group, "double")
keep if jscreg_sreg==1
collapse (count) gr, by(year owner jscreg_sreg economic)
keep if year==2004 & owner==1 & economic==1 & jscreg_sreg==1
save `final_groups'
restore

*Merge together
use `original_groups',clear
append using `original_total_firms'
append using `original_UP_firms'
append using `original_Delo_firms'
append using `jscreg_sreg_groups'
append using `jscreg_sreg_total_firms'
append using `jscreg_sreg_UP_firms'
append using `jscreg_sreg_Delo_firms'
append using `econ_groups'
append using `econ_total_firms'
append using `econ_UP_firms'
append using `econ_Delo_firms'
append using `final_groups'
append using `final_total_firms'
append using `final_UP_firms'
append using `final_Delo_firms'
collapse (sum) Delo UP total gr, by(original jscreg_sreg economic owner)
gen source="Original list" if original==1 & jscreg_sreg==. & economic==. & owner==.
replace source="Present in JSCReg or SReg" if original==. & jscreg_sreg==1 & jscreg_sreg==1 & economic==. & owner==.
replace source="+ sector and controls" if original==. & jscreg_sreg==1 & economic==1 & owner==.
replace source="+ at least 1 identified owner" if original==. & jscreg_sreg==1 & economic==1 & owner==1
drop original jscreg_sreg economic owner
rename gr groups
order source Delo UP total groups
gen n=1 if source=="Original list"
replace n=2 if source=="Present in JSCReg or SReg"
replace n=3 if source=="+ sector and controls"
replace n=4 if source=="+ at least 1 identified owner"
sort n
drop n
export excel "Results/Table1.xls", firstrow (variables)replace
